package com.dmgis.qxfw.dataservice.pages.service.impl;

import com.dmgis.qxfw.dataservice.businessservice.api.domain.DbConfig;
import com.dmgis.qxfw.dataservice.businessservice.common.entity.Rainwatershed;
import com.dmgis.qxfw.dataservice.pages.service.IComDisplayService;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * @className ComDisplayServiceImpl
 * @Description TODO
 * @Author lhfy
 * @Version 1.0
 */

@Service
public class ComDisplayServiceImpl implements IComDisplayService {

    private static final Logger log = Logger.getLogger(ComDisplayServiceImpl.class);

    @Override
    public List<Map<String, Object>> display(int hour) throws SQLException, ClassNotFoundException, IOException {
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();

            // 获取当前时间
            Date endTime = new Date();

            // 使用 Calendar 计算开始时间
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(endTime);
            calendar.add(Calendar.HOUR, -hour);  // 当前时间减去指定的小时数
            Date startTime = calendar.getTime();

            // 转换为 Timestamp 类型，用于 SQL 查询
            Timestamp startTimestamp = new Timestamp(startTime.getTime());
            Timestamp endTimestamp = new Timestamp(endTime.getTime());


            String sql = "SELECT t.* FROM msgmediumsmallscale t " +
                    "JOIN (SELECT stationid, MAX(observtime) AS times FROM msgmediumsmallscale GROUP BY stationid) latest_ids " +
                    "ON t.stationid = latest_ids.stationid AND t.observtime = latest_ids.times " +
                    "WHERE t.observtime BETWEEN ? AND ?"+
                    "AND t.city like ?";

// 使用 PreparedStatement 来执行查询
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setTimestamp(1, startTimestamp);
            preparedStatement.setTimestamp(2, endTimestamp);
            preparedStatement.setString(3, "%" + DbConfig.CITY + "%");


// 执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationId", resultSet.getString("stationid"));
                map.put("stationName", resultSet.getString("stationname"));
                map.put("rainfall", !Objects.equals(resultSet.getString("rain"), "-65535") ? String.format("%.2f", Double.parseDouble(resultSet.getString("rain"))) : null);
                map.put("temperature", !Objects.equals(resultSet.getString("drybulbtemp"), "-65535") ? String.format("%.2f", Double.parseDouble(resultSet.getString("drybulbtemp"))) : null);
                map.put("windElement", !Objects.equals(resultSet.getString("windvelocity"), "-65535") ? String.format("%.2f", Double.parseDouble(resultSet.getString("windvelocity"))) : null);
                map.put("humidity", !Objects.equals(resultSet.getString("relhumidity"), "-65535") ? String.format("%.2f", Double.parseDouble(resultSet.getString("relhumidity"))) : null);
                map.put("airPressure", !Objects.equals(resultSet.getString("stationpress"), "-65535") ? String.format("%.2f", Double.parseDouble(resultSet.getString("stationpress"))) : null);
                map.put("longitude", resultSet.getString("longitude"));
                map.put("latitude", resultSet.getString("latitude"));
                map.put("time", resultSet.getString("observtime"));
                result.add(map);
            }
            connection.close();
            resultSet.close();
            return result;
        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

    }

    @Override
    public List<Map<String, Object>> getStationName() throws SQLException, ClassNotFoundException, IOException {
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();

            String sql = "SELECT DISTINCT stationid, stationname FROM msgmediumsmallscale WHERE CITY LIKE ?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, "%" + DbConfig.CITY + "%");  // 使用参数化查询，并安全地处理CITY值

            ResultSet resultSet = pstmt.executeQuery();  // 使用pstmt来执行查询

            List<Map<String, Object>> result = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("stationId", resultSet.getString("stationid"));
                map.put("stationName", resultSet.getString("stationname"));
                result.add(map);
            }
            connection.close();
            resultSet.close();
            return result;
        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
    }

    @Override
    public List<Map<String, Object>> get24HourInfo(String type,String sql) throws SQLException, IOException, ClassNotFoundException {

        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                        Map<String, Object> map = new HashMap<String, Object>();
                        map.put("stationName", resultSet.getString("stationname"));
                        map.put(type, !Objects.equals(resultSet.getString(type), "-65535") ? String.format("%.2f", Double.parseDouble(resultSet.getString(type))) : null);
                        map.put("hour_of_day", resultSet.getString("hour_of_day"));
                        result.add(map);
                    }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Object> display1(int hour) throws SQLException, ClassNotFoundException, IOException {
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();

            // 获取当前时间
            Date endTime = new Date();

            // 使用 Calendar 计算开始时间
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(endTime);
            calendar.add(Calendar.HOUR, -hour);  // 当前时间减去指定的小时数
            Date startTime = calendar.getTime();

            // 转换为 Timestamp 类型，用于 SQL 查询
            Timestamp startTimestamp = new Timestamp(startTime.getTime());
            Timestamp endTimestamp = new Timestamp(endTime.getTime());


            String sql = "SELECT t.* FROM msgmediumsmallscale t " +
                    "JOIN (SELECT stationid, MAX(observtime) AS times FROM msgmediumsmallscale GROUP BY stationid) latest_ids " +
                    "ON t.stationid = latest_ids.stationid AND t.observtime = latest_ids.times " +
                    "WHERE t.observtime BETWEEN ? AND ?"+
                    "AND t.city like ?";
            ;

// 使用 PreparedStatement 来执行查询
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setTimestamp(1, startTimestamp);
            preparedStatement.setTimestamp(2, endTimestamp);
            preparedStatement.setString(3, "%" + DbConfig.CITY + "%");

// 执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            List< Object> result = new ArrayList<Object>();
            HashMap<String, Integer> rainfallStats = new HashMap<>();
            rainfallStats.put("无雨", 0);
            rainfallStats.put("小雨", 0);
            rainfallStats.put("中雨", 0);
            rainfallStats.put("大雨", 0);
            rainfallStats.put("暴雨", 0);
            rainfallStats.put("大暴雨", 0);
            rainfallStats.put("特大暴雨", 0);
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                //处理降水数据
                String num = !Objects.equals(resultSet.getString("rain"), "-65535") ? String.format("%.2f", Double.parseDouble(resultSet.getString("rain"))) : null;
                if (num != null){
                    double rainfallValue = Double.parseDouble(num);
                    if (rainfallValue >= 0 && rainfallValue <= 1) {
                        rainfallStats.put("无雨", rainfallStats.get("无雨") + 1);
                    } else if (rainfallValue > 1 && rainfallValue <= 10) {
                        rainfallStats.put("小雨", rainfallStats.get("小雨") + 1);
                    } else if (rainfallValue > 10 && rainfallValue <= 25) {
                        rainfallStats.put("中雨", rainfallStats.get("中雨") + 1);
                    } else if (rainfallValue > 25 && rainfallValue <= 50) {
                        rainfallStats.put("大雨", rainfallStats.get("大雨") + 1);
                    } else if (rainfallValue > 50 && rainfallValue <= 100) {
                        rainfallStats.put("暴雨", rainfallStats.get("暴雨") + 1);
                    } else if (rainfallValue > 100 && rainfallValue <= 250) {
                        rainfallStats.put("大暴雨", rainfallStats.get("大暴雨") + 1);
                    } else if (rainfallValue > 250) {
                        rainfallStats.put("特大暴雨", rainfallStats.get("特大暴雨") + 1);
                    }
                }
            }
            result.add( rainfallStats);
            connection.close();
            resultSet.close();
            return result;
        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

    }

    @Override
    public List<Map<String, Object>> getcolor(String sql1) throws SQLException, IOException, ClassNotFoundException {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql1)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                //map.put("stationId", resultSet.getString("stationid"));
                map.put("elementvalue", resultSet.getInt("elementvalue"));
                map.put("elementcolor", resultSet.getInt("elementcolor"));

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> get24HourInfoNew(String sql) throws SQLException, IOException, ClassNotFoundException {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("maxtemp", !Objects.equals(resultSet.getString("maxtemp"), "-65535") ? Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("maxtemp")))) : 0.0);
                map.put("rain", !Objects.equals(resultSet.getString("rain"), "-65535") ? Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("rain")))) : 0.0);
                map.put("instantwindv", !Objects.equals(resultSet.getString("instantwindv"),
                        "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("instantwindv")))) : 0.0);
                map.put("windd", !Objects.equals(resultSet.getString("winddirect"), "-65535")
                        ? (Integer.parseInt(resultSet.getString("winddirect")))
                        : 0);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> get24HourInfoFur(String sql1) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement1 = connection1.createStatement();
             ResultSet resultSet = statement1.executeQuery(sql1)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("maxtemp", !Objects.equals(resultSet.getString("maxtemp"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("maxtemp")))) : 0.0);
                map.put("rain", !Objects.equals(resultSet.getString("rain"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("rain")))) : 0.0);
                map.put("wind", !Objects.equals(resultSet.getString("wind"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("wind")))) : 0.0);
                map.put("windd", !Objects.equals(resultSet.getString("windd"), "-65535")
                        ? (Integer.parseInt(resultSet.getString("windd")))
                        : 0);
                // map.put("times", !Objects.equals(resultSet.getString("times"), "-65535") ?
                //         resultSet.getString("times") : null);

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getStationInfo(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
               map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ? resultSet.getString("stationname") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }
    @Override
    public List<Map<String, Object>> getStationInfo1(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ? resultSet.getString("stationname") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                map.put("cnty", !Objects.equals(resultSet.getString("county"), "-65535") ? resultSet.getString("county") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getWarnInfoAndOther(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement = connection1.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("warningtype", !Objects.equals(resultSet.getString("warningtype"), "-65535") ?
                                resultSet.getString("warningtype") : null);
                map.put("warningtime", !Objects.equals(resultSet.getString("warningtime"), "-65535") ?
                        resultSet.getString("warningtime") : null);
                map.put("monitorvalue", !Objects.equals(resultSet.getString("monitorvalue"), "-65535") ?
                        resultSet.getString("monitorvalue") : null);
                map.put("standardbasis", !Objects.equals(resultSet.getString("standardbasis"), "-65535") ?
                        resultSet.getString("standardbasis") : null);

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getWarnInfoAndOther1(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement = connection1.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("area", !Objects.equals(resultSet.getString("area"), "-65535") ? resultSet.getString("area") : null);
                map.put("type", !Objects.equals(resultSet.getString("type"), "-65535") ?
                        resultSet.getString("type") : null);
                map.put("warningtype", !Objects.equals(resultSet.getString("warningtype"), "-65535") ?
                        resultSet.getString("warningtype") : null);
                map.put("description", !Objects.equals(resultSet.getString("description"), "-65535") ?
                        resultSet.getString("description") : null);
                map.put("biaotou", !Objects.equals(resultSet.getString("biaotou"), "-65535") ?
                        resultSet.getString("biaotou") : null);
                map.put("biaoti", !Objects.equals(resultSet.getString("defenseguide"), "-65535") ?
                        resultSet.getString("defenseguide") : null);
                map.put("announcedtime", !Objects.equals(resultSet.getString("announcedtime"), "-65535") ?
                        resultSet.getString("announcedtime") : null);
                map.put("color", !Objects.equals(resultSet.getString("color"), "-65535") ?
                        resultSet.getString("color") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }
    @Override
    public List<Map<String, Object>> getNewWarnInfo(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement = connection1.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("area", !Objects.equals(resultSet.getString("area"), "-65535") ? resultSet.getString("area") : null);
                map.put("type", !Objects.equals(resultSet.getString("type"), "-65535") ?
                        resultSet.getString("type") : null);
                map.put("city", !Objects.equals(resultSet.getString("city"), "-65535") ?
                        resultSet.getString("city") : null);
                map.put("description", !Objects.equals(resultSet.getString("description"), "-65535") ?
                        resultSet.getString("description") : null);
                map.put("announcedtime", !Objects.equals(resultSet.getString("announcedtime"), "-65535") ?
                        resultSet.getString("announcedtime") : null);
                map.put("color", !Objects.equals(resultSet.getString("color"), "-65535") ?
                        resultSet.getString("color") : null);
                map.put("defenseguide",!Objects.equals(resultSet.getString("defenseguide"), "-65535") ?
                        resultSet.getString("defenseguide") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String,Object>>  getProjectInfo(String sql) throws SQLException, IOException, ClassNotFoundException {
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            Connection connection1 = getConnection1();
            Statement statement = connection1.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", !Objects.equals(resultSet.getString("id"), "-65535") ?
                        resultSet.getString("id") : null);
                map.put("name", !Objects.equals(resultSet.getString("name"), "-65535") ?
                        resultSet.getString("name") : null);
                map.put("overview", !Objects.equals(resultSet.getString("overview"), "-65535") ?
                        resultSet.getString("overview") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                result.add(map);
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getMonitoringInfo(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("datechar", !Objects.equals(resultSet.getString("datechar"), "-65535") ?
                        resultSet.getString("datechar") : null);
                map.put("yttype", !Objects.equals(resultSet.getString("yttype"), "-65535") ?
                        resultSet.getString("yttype") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getTourInfo(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            Connection connection1 = getConnection1();
            Statement statement = connection1.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ?
                        resultSet.getString("stationid") : null);
                map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ?
                        resultSet.getString("stationname") : null);
                map.put("text", !Objects.equals(resultSet.getString("text"), "-65535") ?
                        resultSet.getString("text") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                map.put("areaname", !Objects.equals(resultSet.getString("areaname"), "-65535") ?
                        resultSet.getString("areaname") : null);
                result.add(map);
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getNewTime(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            Connection connection1 = getConnection1();
            Statement statement = connection1.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("datechar", !Objects.equals(resultSet.getString("datechar"), "-65535") ?
                        resultSet.getString("datechar") : null);
                map.put("timechar", !Objects.equals(resultSet.getString("timechar"), "-65535") ?
                        resultSet.getString("timechar") : null);
                map.put("ntimes", !Objects.equals(resultSet.getString("ntimes"), "-65535") ?
                        resultSet.getString("ntimes") : null);

                result.add(map);
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getTourForecast(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement1 = connection1.createStatement();
             ResultSet resultSet = statement1.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
               map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ?
                        resultSet.getString("stationname") : null);
                map.put("maxtemp", !Objects.equals(resultSet.getString("maxtemp"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("maxtemp")))) : 0.0);
                map.put("winds", !Objects.equals(resultSet.getString("winds"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("winds")))) : 0.0);
                map.put("weather1", !Objects.equals(resultSet.getString("weather1"), "-65535") ?
                        resultSet.getString("weather1") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> get24Hou(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection();
             Statement statement1 = connection1.createStatement();
             ResultSet resultSet = statement1.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("times", !Objects.equals(resultSet.getString("times"), "-65535") ?
                        resultSet.getString("times") : null);
                map.put("total_pre", !Objects.equals(resultSet.getString("total_pre"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("total_pre")))) : 0.0);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getWind(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection();
             Statement statement1 = connection1.createStatement();
             ResultSet resultSet = statement1.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("times", !Objects.equals(resultSet.getString("times"), "-65535") ?
                        resultSet.getString("times") : null);
                map.put("total_pre", !Objects.equals(resultSet.getString("total_pre"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("total_pre")))) : 0.0);
                map.put("wind_d_avg_1mi", !Objects.equals(resultSet.getString("win_d_avg_2mi"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("win_d_avg_2mi")))) : 0.0);

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getMarathonInfo(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement = connection1.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", !Objects.equals(resultSet.getString("id"), "-65535") ?
                        resultSet.getString("id") : null);
                map.put("pos", !Objects.equals(resultSet.getString("pos"), "-65535") ?
                        resultSet.getString("pos") : null);

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> get12HourInfoNew(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("maxtemp", !Objects.equals(resultSet.getString("maxtemp"), "-65535") ? Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("maxtemp")))) : 0.0);
                map.put("rain", !Objects.equals(resultSet.getString("rain"), "-65535") ? Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("rain")))) : 0.0);
                map.put("instantwindv", !Objects.equals(resultSet.getString("instantwindv"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("instantwindv")))) : 0.0);
                map.put("windd", !Objects.equals(resultSet.getString("instantwindd"), "-65535")
                        ? (Integer.parseInt(resultSet.getString("instantwindd")))
                        : 0);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> get24HourInfoFur1(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement1 = connection1.createStatement();
             ResultSet resultSet = statement1.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("maxtemp", !Objects.equals(resultSet.getString("maxtemp"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("maxtemp")))) : 0.0);
                map.put("rain", !Objects.equals(resultSet.getString("rain"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("rain")))) : 0.0);
                map.put("wind", !Objects.equals(resultSet.getString("wind"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("wind")))) : 0.0);
                map.put("windd", !Objects.equals(resultSet.getString("windd"), "-65535")
                        ? (Integer.parseInt(resultSet.getString("windd")))
                        : 0);
                map.put("times", !Objects.equals(resultSet.getString("times"), "-65535") ?
                        resultSet.getString("times") : null);

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> get24HourInfoFu(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement1 = connection1.createStatement();
             ResultSet resultSet = statement1.executeQuery(sql)) {


            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("maxtemp", !Objects.equals(resultSet.getString("maxtemp"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("maxtemp")))) : 0.0);
                map.put("rain", !Objects.equals(resultSet.getString("rain"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("rain")))) : 0.0);
                map.put("wind", !Objects.equals(resultSet.getString("wind"), "-65535") ?  Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("wind")))) : 0.0);
                map.put("windd", !Objects.equals(resultSet.getString("windd"), "-65535")
                        ? (Integer.parseInt(resultSet.getString("windd")))
                        : 0);
                map.put("times", !Objects.equals(resultSet.getString("times"), "-65535") ?
                        resultSet.getString("times") : null);

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getlightningInfo(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("longitude", !Objects.equals(resultSet.getString("lon"), "-65535") ?
                        Double.parseDouble(resultSet.getString("lon")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("lat"), "-65535") ?
                        Double.parseDouble(resultSet.getString("lat")) : null);
                map.put("strength", !Objects.equals(resultSet.getString("strength"), "-65535") ?
                        Double.parseDouble(resultSet.getString("strength")) : null);
                result.add(map);
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> getMonitoringInfoNew(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement = connection1.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("warningtype", !Objects.equals(resultSet.getString("warningtype"), "-65535") ?
                        resultSet.getString("warningtype") : null);
                map.put("warningtime", !Objects.equals(resultSet.getString("warningtime"), "-65535") ?
                        resultSet.getString("warningtime") : null);
                map.put("monitorvalue", !Objects.equals(resultSet.getString("monitorvalue"), "-65535") ?
                        resultSet.getString("monitorvalue") : null);
                map.put("standardbasis", !Objects.equals(resultSet.getString("standardbasis"), "-65535") ?
                        resultSet.getString("standardbasis") : null);
                map.put("monitorname", !Objects.equals(resultSet.getString("monitorname"), "-65535") ?
                        resultSet.getString("monitorname") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;

    }

    @Override
    public List<Map<String, Object>> getWarnInfoAndOther2(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection1 = getConnection1();
             Statement statement = connection1.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("disweather_name", !Objects.equals(resultSet.getString("disweather_name"), "-65535") ?
                        resultSet.getString("disweather_name") : null);
                map.put("disweather_result", !Objects.equals(resultSet.getString("disweather_result"), "-65535") ?
                        resultSet.getString("disweather_result") : null);
                map.put("happen_time", !Objects.equals(resultSet.getString("happen_time"), "-65535") ?
                        resultSet.getString("happen_time") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }
    @Override
    public List<Map<String, Object>> getMhInfo1(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ?
                        resultSet.getString("stationname") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                map.put("instantwindd", !Objects.equals(resultSet.getString("instantwindd"), "-65535") ?
                        (int) Double.parseDouble(resultSet.getString("instantwindd")) : 0);
                map.put("value", !Objects.equals(resultSet.getString("value"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("value")))) : 0.0);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }
    @Override
    public List<Map<String, Object>> getMhInfo2(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ?
                        resultSet.getString("stationname") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                map.put("instantwindd", !Objects.equals(resultSet.getString("instantwindd"), "-65535") ?
                        (int) Double.parseDouble(resultSet.getString("instantwindd")) : 0);
                map.put("value", !Objects.equals(resultSet.getString("value"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("value")))) : 0.0);
                map.put("cnty", !Objects.equals(resultSet.getString("cnty"), "-65535") ?
                        resultSet.getString("cnty") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }
    @Override
    public List<Map<String, Object>> getNewShiJian(String sql) throws Exception{
        List<Map<String, Object>> result = new ArrayList<>();
        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("datetime", !Objects.equals(resultSet.getString("datetime"), "-65535") ?
                        resultSet.getString("datetime") : null);

                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getStationInfo55(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ? resultSet.getString("stationname") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                map.put("county", !Objects.equals(resultSet.getString("county"), "-65535") ? resultSet.getString("county") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getMhInfo3(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ?
                        resultSet.getString("stationname") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                map.put("value", !Objects.equals(resultSet.getString("value"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("value")))) : 0.0);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }


    @Override
    public List<Map<String, Object>> getMhInfo(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("stationid", !Objects.equals(resultSet.getString("stationid"), "-65535") ? resultSet.getString("stationid") : null);
                map.put("stationname", !Objects.equals(resultSet.getString("stationname"), "-65535") ?
                        resultSet.getString("stationname") : null);
                map.put("longitude", !Objects.equals(resultSet.getString("longitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("longitude")) : null);
                map.put("latitude", !Objects.equals(resultSet.getString("latitude"), "-65535") ?
                        Double.parseDouble(resultSet.getString("latitude")) : null);
                map.put("value", !Objects.equals(resultSet.getString("value"), "-65535") ?
                        Double.parseDouble(String.format("%.1f", Double.parseDouble(resultSet.getString("value")))) : 0.0);
                map.put("cnty", !Objects.equals(resultSet.getString("cnty"), "-65535") ?
                        resultSet.getString("cnty") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getNewDecision(String sql) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        // 使用 try-with-resources 自动管理资源
        try (Connection connection = getConnection1();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理查询结果
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", !Objects.equals(resultSet.getString("id"), "-65535") ? resultSet.getString("id") : null);
                map.put("productname", !Objects.equals(resultSet.getString("productname"), "-65535") ?
                        resultSet.getString("productname") : null);
                map.put("producttype2", !Objects.equals(resultSet.getString("producttype2"), "-65535") ?
                        resultSet.getString("producttype2") : null);
                map.put("maketime", !Objects.equals(resultSet.getString("maketime"), "-65535") ?
                        resultSet.getString("maketime") : null);
                result.add(map);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }

        return result;
    }

    private static Connection getConnection() throws SQLException, ClassNotFoundException, IOException {
        try {
            Properties prop = new Properties();
            InputStream in = ComDisplayServiceImpl.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            prop.load(in);
            Connection connection;
            Class.forName("org.postgresql.Driver");
            return DriverManager.getConnection(prop.getProperty("postgresql.master.url"), prop.getProperty("postgresql.username"), prop.getProperty("postgresql.password"));

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
    }
    private static Connection getConnection1() throws SQLException, ClassNotFoundException, IOException {
        try {
            Properties prop = new Properties();
            InputStream in = ComDisplayServiceImpl.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            prop.load(in);
            Connection connection;
            Class.forName("org.postgresql.Driver");
            return DriverManager.getConnection(prop.getProperty("postgresql.servant.url"), prop.getProperty("postgresql.username"), prop.getProperty("postgresql.password"));

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        }
    }
}
